package gov.nhso.prov.acc.apps.acc6;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;

import gov.nhso.prov.acc.beans.Acc6BudgetBalanceBean;
import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.utils.UserInfo;
import gov.nhso.prov.acc.utils.Utility;

@SuppressWarnings("rawtypes")
public class ACC60109 extends Utility{
	public String url = "/apps/acc6/ACC60109A.jsp";


	public ACC60109(HttpServletRequest request, HttpServletResponse response) {
		this.message(request).clearMsg();
		String job = this.getAttr(request, "job");
		if (job.equals("init")) {
			this.init(request);
		} else if (job.equals("search")) {
			this.search(request);
		} else if (job.equals("cancel")) {
			this.cancel(request);
		}
	}

	private String init(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("job", "search");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String search(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String budgetYear = this.getAttr(request, "budgetYear");
			StringBuffer sb = new StringBuffer();
			List lsResult = new ArrayList();
			sb.append("SELECT DISTINCT(T2.PROVINCE_NAME),T1.BUDGET_YEAR,SUM(T1.BALANCE_AMOUNT) AS SUM_BALANCE_AMOUNT, T1.PROVINCE_ID ");
			sb.append("FROM " + this.schema + ".ACC6_BUDGET_BALANCE T1 JOIN " + this.schema + ".PROVINCE T2 ON T1.PROVINCE_ID = T2.PROVINCE_ID ");
			sb.append("WHERE T1.BUDGET_YEAR = '" + budgetYear + "' AND T1.BALANCE_STATUS = 'Y' ");
			sb.append("GROUP BY T2.PROVINCE_NAME,T1.PROVINCE_ID,T1.BUDGET_YEAR");
			lsResult = dao.listDataSql(sb.toString());
			if (lsResult.size() <= 0) {
				this.message(request).setMsg("I", "ไม่พบข้อมูล");
			}
			request.setAttribute("job", "search");
			request.setAttribute("lsResult", lsResult);
			request.setAttribute("budgetYear", budgetYear);
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String cancel(HttpServletRequest request) {
		Connection conn = null;
		ISUD dao = null;
		try {
			UserInfo ui = this.user(request);
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String budgetYear = this.getAttr(request, "budgetYear");
			String provinceId = this.getAttr(request, "provinceId");
			Acc6BudgetBalanceBean ab = new Acc6BudgetBalanceBean();
			StringBuffer sb = new StringBuffer();
			ab.setProvinceId(provinceId);
			ab.setBudgetYear(budgetYear);
			ab.setTransactionTimestamp("null");
			ab.setBalanceStatus("P");
			ab.setUpdatedBy(ui.getUserId());
			ab.setUpdatedDate(this.sysDate);
			dao.update(ab);
			sb.append("DELETE FROM " + this.schema + ".ACC6_TRANSACTION ");
			sb.append("WHERE BUDGET_YEAR = '" + budgetYear + "' AND PROVINCE_ID = '" + provinceId + "' AND TRANSACTION_TYPE = 'B' ");
			dao.execDataSql(sb.toString());
			request.setAttribute("budgetYear", budgetYear);
			request.setAttribute("job", "search");
			this.message(request).setMsg("I", "บันทึกข้อมูลสำเร็จ");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.search(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
}
